﻿using Dapper;
using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class VoucherDAL
    {
        public bool AddVoucher(VoucherInfo voucherInfo)
        {
            string strSql = string.Format(@"INSERT into voucher_info(OwnerID,DeviceID,AuthorizedFloor,EffectiveStarttime,EffectiveEndtime,CarduploadFlag,PicuploadFlag,PicShow,CardShow,OprationTime,Operator,projectID,AuthData)VALUES({0},{1},'{2}','{3}','{4}',0,0,{5},{6},'{7}','{8}',{9},'{10}')",
                                            voucherInfo.OwnerID, voucherInfo.DeviceID, voucherInfo.AuthorizedFloor, voucherInfo.EffectiveStarttime, voucherInfo.EffectiveEndtime, voucherInfo.PicShow, voucherInfo.CardShow, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), voucherInfo.Operator, voucherInfo.projectID, voucherInfo.AuthData);
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateVoucher(VoucherInfo voucherInfo)
        {
            string strSql = string.Format(@"UPDATE voucher_info SET DeviceID={0},AuthorizedFloor='{1}',EffectiveStarttime='{2}',EffectiveEndtime='{3}',Lssuestate=0,OprationTime='{5}',Operator='{6}',IsDelete=0 WHERE ID={4}",
                                           voucherInfo.DeviceID, voucherInfo.AuthorizedFloor, voucherInfo.EffectiveStarttime, voucherInfo.EffectiveEndtime, voucherInfo.ID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), voucherInfo.Operator);
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool DeleteVoucher(VoucherInfo voucher)
        {
            string strSql;
            if (voucher.OwnerType == 4)
            {
                strSql = "DELETE FROM voucher_info WHERE ID=" + voucher.ID;
            }
            else
            {
                strSql = "UPDATE voucher_info SET IsDelete=1,Flag=0 WHERE ID=" + voucher.ID;
            }
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateVoucherAuthData(int id, string authdata)
        {
            string strSql = "UPDATE voucher_info SET Lssuestate=0,AuthData='" + authdata + "' WHERE ID=" + id;
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public List<VoucherInfo> QueryVoucherList(VoucherInfo voucherInfo)
        {
            string strSql = "SELECT vi_voucher.*,building_info.BuildingName FROM vi_voucher LEFT JOIN building_info ON building_info.ID=vi_voucher.BuildID";
            string sqlwhere = string.Empty;

            if (voucherInfo.projectID > 0)
            {
                sqlwhere += " vi_voucher.projectID=" + voucherInfo.projectID + " AND";
            }
            if (voucherInfo.DeviceID > 0)
            {
                sqlwhere += " vi_voucher.DeviceID=" + voucherInfo.DeviceID + " AND";
            }
            if (voucherInfo.OwnerID > 0)
            {
                sqlwhere += " vi_voucher.OwnerID=" + voucherInfo.OwnerID + " AND";
            }
            if (voucherInfo.OwnerType > 0)
            {
                sqlwhere += " vi_voucher.OwnerType=" + voucherInfo.OwnerType + " AND";
            }
            if (voucherInfo.OwnerType == 0)
            {
                sqlwhere += " vi_voucher.OwnerType !=5 AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.OwnerName))
            {
                sqlwhere += " vi_voucher.OwnerName like '%" + voucherInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.DeviceName))
            {
                sqlwhere += " vi_voucher.DeviceName like '%" + voucherInfo.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.BuildingName))
            {
                sqlwhere += " building_info.BuildingName like '%" + voucherInfo.BuildingName + "%' AND";
            }
            sqlwhere += " vi_voucher.IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            strSql += " ORDER BY vi_voucher.Lssuestate";
            if (voucherInfo.Recordhead > -1 && voucherInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + voucherInfo.Recordhead + "," + voucherInfo.Recordtail;
            }

            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public List<VoucherInfo> QueryExistVoucher(VoucherInfo voucherInfo)
        {
            string strSql = "SELECT * FROM vi_voucher";
            string sqlwhere = string.Empty;

            if (voucherInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + voucherInfo.projectID + " AND";
            }
            if (voucherInfo.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + voucherInfo.DeviceID + " AND";
            }
            if (voucherInfo.OwnerID > 0)
            {
                sqlwhere += " OwnerID=" + voucherInfo.OwnerID + " AND";
            }
            if (voucherInfo.OwnerType > 0)
            {
                sqlwhere += " OwnerType=" + voucherInfo.OwnerType + " AND";
            }
            if (voucherInfo.OwnerType == 0)
            {
                sqlwhere += " OwnerType !=5 AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + voucherInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + voucherInfo.DeviceName + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            strSql += " ORDER BY Lssuestate";
            if (voucherInfo.Recordhead > -1 && voucherInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + voucherInfo.Recordhead + "," + voucherInfo.Recordtail;
            }

            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public int QueryVoucherListCount(VoucherInfo voucherInfo)
        {
            string strSql = "SELECT COUNT(1) FROM vi_voucher LEFT JOIN building_info ON building_info.ID=vi_voucher.BuildID";
            string sqlwhere = string.Empty;

            if (voucherInfo.projectID > 0)
            {
                sqlwhere += " vi_voucher.projectID=" + voucherInfo.projectID + " AND";
            }
            if (voucherInfo.DeviceID > 0)
            {
                sqlwhere += " vi_voucher.DeviceID=" + voucherInfo.DeviceID + " AND";
            }
            if (voucherInfo.OwnerID > 0)
            {
                sqlwhere += " vi_voucher.OwnerID=" + voucherInfo.OwnerID + " AND";
            }
            if (voucherInfo.OwnerType > 0)
            {
                sqlwhere += " vi_voucher.OwnerType=" + voucherInfo.OwnerType + " AND";
            }
            if (voucherInfo.OwnerType == 0)
            {
                sqlwhere += " vi_voucher.OwnerType !=5 AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.OwnerName))
            {
                sqlwhere += " vi_voucher.OwnerName like '%" + voucherInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.DeviceName))
            {
                sqlwhere += " vi_voucher.DeviceName like '%" + voucherInfo.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(voucherInfo.BuildingName))
            {
                sqlwhere += " building_info.BuildingName like '%" + voucherInfo.BuildingName + "%' AND";
            }
            sqlwhere += " vi_voucher.IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<VoucherInfo>.SqlScalar(strSql);
        }

        public VoucherInfo LoadVoucher(int id)
        {
            string strSql = "SELECT * FROM vi_voucher WHERE ID=" + id;
            return SqlHelper<VoucherInfo>.SqlSingleData(strSql);
        }

        public VoucherInfo CheckVoucher(VoucherInfo voucherInfo)
        {
            string strSql = "SELECT * FROM voucher_info WHERE DeviceID=" + voucherInfo.DeviceID + " AND OwnerID=" + voucherInfo.OwnerID;
            return SqlHelper<VoucherInfo>.SqlSingleData(strSql);
        }

        public bool UpdateVoucherByDev(VoucherInfo voucherInfo)
        {
            string strSql = string.Format(@"UPDATE voucher_info SET AuthorizedFloor='{0}',EffectiveStarttime='{1}',EffectiveEndtime='{2}',Lssuestate={5},PicuploadFlag={6},CarduploadFlag={7},AuthData='{8}',IsDelete=0 WHERE DeviceID={3} AND OwnerID={4}",
                                          voucherInfo.AuthorizedFloor, voucherInfo.EffectiveStarttime, voucherInfo.EffectiveEndtime, voucherInfo.DeviceID, voucherInfo.OwnerID, voucherInfo.Lssuestate, voucherInfo.PicuploadFlag, voucherInfo.CarduploadFlag, voucherInfo.AuthData);
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public List<VoucherInfo> QueryListByDev(int parentid, int deviceid, int projectid)
        {
            string strSql = "SELECT * FROM vi_voucher WHERE DeviceID=" + deviceid + " AND ( ParentID=" + parentid + " OR OwnerID=" + parentid + ") AND projectID=" + projectid;

            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public List<VoucherInfo> QueryListByDevinfo(int deviceid, int projectid)
        {
            string strSql = "SELECT * FROM vi_voucher WHERE ElevatorID=" + deviceid + " AND projectID=" + projectid + " AND IsDelete=0";

            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public bool UpdateVoucherState(int ownerid, int devid, string errormsg)
        {
            string strSql;
            if (!string.IsNullOrEmpty(errormsg))
            {
                strSql = "UPDATE voucher_info SET Lssuestate=0 WHERE OwnerID=" + ownerid + " AND DeviceID=" + devid;
            }
            else
            {
                strSql = "UPDATE voucher_info SET SuccessTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',Lssuestate=1 WHERE OwnerID=" + ownerid + " AND DeviceID=" + devid;
            }

            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateCardStatus(int ownerid, int deviceid, string errormsg)
        {
            string strSql = string.Empty;
            if (!string.IsNullOrEmpty(strSql))
            {
                strSql = "UPDATE voucher_info SET CarduploadFlag=0 WHERE OwnerID=" + ownerid + " AND DeviceID=" + deviceid;
            }
            else
            {
                strSql = "UPDATE voucher_info SET CarduploadFlag=1 WHERE OwnerID=" + ownerid + " AND DeviceID=" + deviceid;
            }

            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateFaceStatus(int ownerid, int deviceid, string errormsg)
        {
            string strSql = string.Empty;
            if (!string.IsNullOrEmpty(errormsg))
            {
                strSql = "UPDATE voucher_info SET PicuploadFlag=0 WHERE OwnerID=" + ownerid + " AND DeviceID=" + deviceid;
            }
            else
            {
                strSql = "UPDATE voucher_info SET PicuploadFlag=1 WHERE OwnerID=" + ownerid + " AND DeviceID=" + deviceid;
            }

            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool DeleteVoucherByOwner(int deviceid, int ownerid)
        {
            string strSql = "UPDATE voucher_info SET IsDelete=1,Flag=0 WHERE OwnerID=" + ownerid + " AND DeviceID=" + deviceid;

            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public string QueryAuthorizedFloor(string device_ip, string uuid)
        {
            string strSql = "SELECT AuthorizedFloor FROM vi_voucher WHERE DeviceID=(SELECT ID FROM device_info WHERE ElevatorID=(SELECT ElevatorID FROM device_info WHERE DeviceIP='" + device_ip + "' AND DeviceType=1) AND DeviceType=2) AND UUID='" + uuid + "'";
            return SqlHelper<VoucherInfo>.SqlScalarString(strSql);
        }

        public bool ClearVoucherByDevice(int deviceid, int personIdentity)
        {
            string strSql;
            if (personIdentity != 0)
            {
                if (personIdentity == 4)
                {
                    strSql = "Delete FROM voucher_info WHERE DeviceID=" + deviceid + " AND OwnerID IN (SELECT ID FROM owner_info WHERE OwnerType=" + personIdentity + ");delete FROM owner_info WHERE OwnerType=4";
                }
                else
                {
                    strSql = "UPDATE voucher_info SET IsDelete=1,Flag=0 WHERE DeviceID=" + deviceid + " AND OwnerID IN (SELECT ID FROM owner_info WHERE OwnerType=" + personIdentity + ");Delete FROM voucher_info WHERE DeviceID=" + deviceid + " AND OwnerID IN (SELECT ID FROM owner_info WHERE OwnerType=" + personIdentity + ");delete FROM owner_info WHERE OwnerType=4";
                }
            }
            else
            {
                strSql = "UPDATE voucher_info SET IsDelete=1,Flag=0 WHERE DeviceID=" + deviceid;
            }
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool ClearBatchVoucher(int deviceid, string uuid)
        {
            string strSql = "UPDATE voucher_info SET IsDelete=1,Flag=0 WHERE OwnerID=(SELECT owner_info.ID FROM owner_info WHERE UUID='" + uuid + "') AND DeviceID=" + deviceid;

            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateLssuestate(int id, int state)
        {
            string strSql;
            if (state == 1)
            {
                strSql = "UPDATE voucher_info SET Lssuestate=" + state + ",SuccessTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',Flag=1 WHERE ID=" + id;
            }
            else
            {
                strSql = "UPDATE voucher_info SET Lssuestate=" + state + " WHERE ID=" + id;
            }
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdatePersonLssuestate(int id)
        {
            string strSql = "UPDATE voucher_info SET PicuploadFlag=1,Lssuestate=1,SuccessTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',Flag=1 WHERE ID=" + id;
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public List<VoucherInfo> QueryOverVisit()
        {
            string strSql = "select * FROM vi_voucher WHERE OwnerType=4 AND EffectiveEndtime<'" + DateTime.Now.AddMonths(-1).ToString() + "' AND IsDelete=0 AND apply_type=2";
            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public bool DeleteVoucherByOwner(string deviceip, string owneruuid)
        {
            string strSql = "delete FROM voucher_info WHERE DeviceID=(SELECT * FROM device_info WHERE DeviceIP='" + deviceip + "' AND IsDelete=0) AND OwnerID=(SELECT * FROM owner_info WHERE UUID='" + owneruuid + "' AND IsDelete=0)";
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateVoucher_1(VoucherInfo voucherInfo)
        {
            string strSql = string.Format(@"UPDATE voucher_info SET AuthorizedFloor='{1}',EffectiveStarttime='{2}',EffectiveEndtime='{3}' WHERE DeviceID=(SELECT ID FROM device_info WHERE DeviceIP='{0}' AND IsDelete=0)",
                                           voucherInfo.DeviceIP, voucherInfo.AuthorizedFloor, voucherInfo.EffectiveStarttime, voucherInfo.EffectiveEndtime);
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public List<int> QueryUserID(string EmployeeNo)
        {
            string strSql = "SELECT USERID FROM USERINFO WHERE BADGENUMBER='" + EmployeeNo + "'";
            LogHelper.Log(strSql);
            return SqlHelper<int>.SqlServerList(strSql);
        }

        public bool InsertCheckRecord(int userid, int RecordId, string deviceid,DateTime date)
        {
            string strSql = string.Format("INSERT INTO CHECKINOUT(USERID,CHECKTIME,CHECKTYPE,VERIFYCODE,SENSORID,WorkCode,sn,UserExtFmt,Memoinfo,RecordID)VALUES({0},'{1}','I',2,1,0,'0622133200135',0,'{2}',{3})", userid, date.ToString("yyyy-MM-dd HH:mm:ss"), deviceid, RecordId);
            LogHelper.Log(strSql);
            return SqlHelper<int>.SqlServerExecute(strSql);
        }

        public bool SyncVoucher(VoucherInfo voucherInfo)
        {
            string strSql = string.Format(@"INSERT into voucher_info(OwnerID,DeviceID,AuthorizedFloor,EffectiveStarttime,EffectiveEndtime,CarduploadFlag,PicuploadFlag,PicShow,CardShow,OprationTime,Operator,projectID,Lssuestate,Flag)VALUES({0},{1},'1','{2}','{3}',0,1,0,0,'{4}','system',1,1,1)", voucherInfo.OwnerID, voucherInfo.DeviceID, voucherInfo.EffectiveStarttime, voucherInfo.EffectiveEndtime, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public List<VoucherInfo> QueryN71SPVoucher(int DeviceID, int OwnerID)
        {
            string strSql = "SELECT * FROM vi_voucher where DeviceID=" + DeviceID + " AND OwnerID=" + OwnerID + " AND IsDelete=0";

            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public bool UpdateN71SPOwnerInfo(string cardno, int id)
        {
            string strSql = string.Format(@"update owner_info set CardNO='{0}' WHERE ID={1}", cardno, id);
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }
    }
}
